<?php
error_reporting('IGNORE_REPEATED_ERRORS');
?>
<html>
<HEAD>
<script type="text/javascript" src="datepickercontrol/datepickercontrol.js"></script>
<link type="text/css" rel="stylesheet" href="datepickercontrol/datepickercontrol.css">
</HEAD>
<body>
<form name="report_form" id="report_form" action="reports.php" method="post">
<link type="text/css" rel="stylesheet" href="datepickercontrol/datepickercontrol.css"> 
<BR>
<input type="hidden" id="DPC_TODAY_TEXT" value="today">
<input type="hidden" id="DPC_BUTTON_TITLE" value="Open calendar...">
<input type="hidden" id="DPC_MONTH_NAMES" value="['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']">
<input type="hidden" id="DPC_DAY_NAMES" value="['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']">
<input type="hidden" id="DPC_DEFAULT_FORMAT" value="YYYY-MM-DD">
<input type="hidden" id="DPC_SUBMIT_FORMAT" value="YYYY-MM-DD">
Start Date: <input type="text" name="start_date" id="start_date" size="12" style="border-width:1px;" datepicker="true">
&nbsp&nbsp&nbsp
End Date: <input type="text" name="end_date" id="end_date" size="12" style="border-width:1px;" datepicker="true">
&nbsp&nbsp&nbsp
<input type="submit" value="Submit"/> <BR>

<?php



mysql_connect("localhost", "root", "") or
    die("Could not connect: " . mysql_error());
mysql_select_db("QA");



$start_date = $_POST["start_date"];
$end_date = $_POST["end_date"];


if (strlen($start_date)<1) {
	$result = mysql_query("SELECT MIN(CALL_DATE) FROM QA.Caller");
	$row = mysql_fetch_array($result, MYSQL_BOTH);
	$start_date = $row[0];
}

if (strlen($end_date)<1) {
	$result = mysql_query("SELECT MAX(CALL_DATE) FROM QA.Caller");
	$row = mysql_fetch_array($result, MYSQL_BOTH);
	$end_date = $row[0];
}
echo "<BR>This report is for the date range ".$start_date." through ".$end_date." inclusively.<BR>";
echo "<A HREF=\"saveasspreadsheet.php?start_date=".$start_date."&end_date=".$end_date."\">Download</A> data as spreadsheet.<BR><BR>";

$qstring = "select count(*) from QA.Caller where QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\"";
$result = mysql_query($qstring);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$calls_attempted = $row[0];

$qstring = "select count(*) from QA.Caller, QA.Unreachable where QA.Caller.Reference_Number=QA.Unreachable.Caller_Reference AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\"";
$result = mysql_query($qstring);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$calls_unreachable = $row[0];

echo "<TABLE border=1><TR><TD colspan=2>QA Statistics</TD></TR>";
echo "<TR><TD>Calls Attempted</TD><TD>".$calls_attempted."</TD></TR>";
echo "<TR><TD>Unreachable</TD><TD>".$calls_unreachable."</TD></TR>";
echo "</TABLE><BR>";

$taxonomies = array ();

$qstring = "SELECT QA.Taxonomies.Taxonomy_Description, Count(QA.Taxonomies.Taxonomy_Description) FROM QA.Caller, QA.Unreachable, QA.Taxonomies WHERE QA.Caller.Reference_Number=QA.Unreachable.Caller_Reference AND QA.Taxonomies.Reference_Number=QA.Unreachable.Taxonomy AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\" GROUP BY QA.Taxonomies.Taxonomy_Description ASC";
$result = mysql_query($qstring);
//echo "Query: ".$qstring."<BR>";

//echo "<TABLE border=1><TR><TD colspan=2 align=center width=100%>Unreachable Calls by Taxonomy</TD></TR><TR><TD>Taxonomy</TD><TD>Count</TD></TR>";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
//	echo "<TR><TD>".$row[0]."</TD><TD>".$row[1]."</TD><TD>".$row[2]."</TD></TR>";
	$taxonomies[$row[0]][0] = $row[1];
}

mysql_free_result($result);

//echo "<TR><TD colspan=2 align=center width=100%> End </TD></TR></TABLE>";



//echo "<BR>";



$result = mysql_query("SELECT QA.Taxonomies.Taxonomy_Description, Count(QA.Taxonomies.Taxonomy_Description) FROM QA.Caller, QA.Needs, QA.Taxonomies WHERE QA.Caller.Reference_Number=QA.Needs.Caller_Reference AND QA.Taxonomies.Reference_Number=QA.Needs.Taxonomy AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\" GROUP BY QA.Taxonomies.Taxonomy_Description ASC");

//echo "<TABLE border=1><TR><TD colspan=2 align=center width=100%>Needs Calls by Taxonomy</TD></TR><TR><TD>Taxonomy</TD><TD>Count</TD></TR>";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
//	echo "<TR><TD>".$row[0]."</TD><TD>".$row[1]."</TD><TD>".$row[2]."</TD></TR>";
	$taxonomies[$row[0]][1] = $row[1];
}

mysql_free_result($result);

//echo "<TR><TD colspan=2 align=center width=100%> End </TD></TR></TABLE>";



//echo "<BR>";

$qstring = "SELECT QA.Taxonomies.Taxonomy_Description, Count(QA.Taxonomies.Taxonomy_Description) FROM QA.Caller, QA.AgencyFeedback, QA.Taxonomies WHERE QA.Caller.Reference_Number=QA.AgencyFeedback.Caller_Reference AND QA.Taxonomies.Reference_Number=QA.AgencyFeedback.Taxonomy AND QA.Caller.Call_Date BETWEEN \"".$start_date."\" AND \"".$end_date."\" GROUP BY QA.Taxonomies.Taxonomy_Description ASC";
$result = mysql_query($qstring);
//echo "Qstring: $qstring<BR>";

//echo "<TABLE border=1><TR><TD colspan=2 align=center width=100%>Agency Feedback Calls by Taxonomy</TD></TR><TR><TD>Taxonomy</TD><TD>Count</TD></TR>";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
//	echo "<TR><TD>".$row[0]."</TD><TD>".$row[1]."</TD><TD>".$row[2]."</TD></TR>";
	$taxonomies[$row[0]][2] = $row[1];
}

mysql_free_result($result);

//echo "<TR><TD colspan=2 align=center width=100%> End </TD></TR></TABLE>";



//echo "<BR>";



echo "<Table border=1><TR><TD>Taxonomy</TD><TD>Unreachable</TD><TD>Needs</TD><TD>Agency</TD><TD>Total</TD></TR>";

$unreach_tot = 0;
$needs_tot = 0;
$agency_tot = 0;
$tot=0;

foreach($taxonomies as $key=>$value){
	$unreach_tot = $unreach_tot + $value[0];
	$needs_tot = $needs_tot + $value[1];
	$agency_tot = $agency_tot + $value[2];
	$tot = $tot + ($value[0]+$value[1]+$value[2]);
	echo "<TR><TD><A href=\"taxonomy_report.php?tot=".($value[0]+$value[1]+$value[2])."&unreach=".$value[0]."&needs=".$value[1]."&agency=".$value[2]."&start_date=".$start_date."&end_date=".$end_date."&taxonomy=".urlencode($key)."\">".$key."</A></TD><TD>".$value[0]."</TD><TD>".$value[1]."</TD><TD>".$value[2]."</TD><TD>".($value[0]+$value[1]+$value[2])."</TD></TR>";

}

echo "<TR><TD>Total</TD><TD>".$unreach_tot."</TD><TD>".$needs_tot."</TD><TD>".$agency_tot."</TD><TD>".($unreach_tot+$needs_tot+$agency_tot)."\\".$tot."</TD></TR>";
echo "<TR><TD colspan=5 align=center width=100%> End </TD></TR></TABLE>";

echo "<BR><BR><U><B>Other Reports</B></U><BR><UL>";

echo "<LI><A href=\"211feedback_report.php?start_date=$start_date&end_date=$end_date\">211 Feedback between $start_date and $end_date.</LI>";
echo "<LI><A href=\"agencyfeedback_report.php?start_date=$start_date&end_date=$end_date\">Agency Feedback between $start_date and $end_date.</LI>";
echo "<LI><A href=\"topagency_report.php?start_date=$start_date&end_date=$end_date\">Top Agencies between $start_date and $end_date.</LI>";
echo "</UL>";


?>







<br>
</body>
</html>
